//
//  CS_MusicDaoProtocol.swift
//  NewSS
//
//  Created by 杨馥瑞 on 2021/9/13.
//

import Foundation
import GRDB

private let kMusicMaxCount = 5000

protocol CS_MusicDaoProtocol {
    
    // MARK: - 增
    
    /// 插入一首音乐
    static func insertMusic(_ music: CS_MusicModel)
    
    /// 插入多首音乐
    static func insertMusicArray(_ musicArray: [CS_MusicModel])
    
    // MARK: - 删
    
    /// 删除一首音乐
    static func deleteMusic(_ musicId: String)
    
    /// 删除多首音乐
    static func deleteMusicArray(_ musicIdArray: [String])
    
    // MARK: - 改
    
    static func updateMusic(_ music: CS_MusicModel)
    
    static func updateMusicArray(_ musicArray: [CS_MusicModel])
    
    // MARK: - 查
    
    static func queryAllMusic() -> [CS_MusicModel]
    
    static func queryAllMusicAndSortByCreateTime() -> [CS_MusicModel]
    
    static func queryIsNewMusic(_ musicId: String) -> Bool
}

extension CS_MusicDaoProtocol {
    
    // MARK: - 增
    
    /// 插入一首音乐
    static func insertMusic(_ music: CS_MusicModel) {
        insertMusicArray([music])
    }
    
    /// 插入多首音乐
    static func insertMusicArray(_ musicArray: [CS_MusicModel]) {
        guard !musicArray.isEmpty else { return }
        
        do {
            for music in musicArray {
                guard !music.identifier.isEmpty else {
                    CJAssert(false, "音乐 - 插入音乐 - 音乐 id 不能为空")
                    continue
                }
                try _insertMusicByDatabase(music)
            }
        } catch {
            CJAssert(false, "音乐 - 插入音乐 - Error: \(error)")
        }
    }
    
    // MARK: - 删
    
    /// 删除一首音乐
    static func deleteMusic(_ musicId: String) {
        deleteMusicArray([musicId])
    }
    
    /// 删除多首音乐
    static func deleteMusicArray(_ musicIdArray: [String]) {
        guard !musicIdArray.isEmpty else {
            return
        }
        guard !musicIdArray.contains("") else {
            CJLog("音乐 - 删除 - Error: 音乐 id 不能为 0")
            return
        }
        guard let customDatabase = CS_MusicStorageManager.shared.database else {
            CJLog("音乐 - 删除 - Error: 数据库未连接")
            return
        }
        
        var musicIdStr = musicIdArray.joined(separator: ",")
        if musicIdStr.hasPrefix(",") {
            musicIdStr.removeLast()
        }
        
        let deleteSql = "delete from music where identifier in (?)"
        do {
            try customDatabase.dbQueue.write { db in
                try db.execute(sql: deleteSql, arguments: [musicIdStr])
            }
        } catch {
            CJAssert(false, "音乐 - 删除 - Error: \(error)")
        }
    }
    
    // MARK: - 改
    
    static func updateMusic(_ music: CS_MusicModel) {
        updateMusicArray([music])
    }
    
    static func updateMusicArray(_ musicArray: [CS_MusicModel]) {
        guard !musicArray.isEmpty else { return }
        
        guard let customDatabase = CS_MusicStorageManager.shared.database else {
            CJLog("音乐 - 更新 - Error: 数据库未连接")
            return
        }
        
        let updateSql = """
        UPDATE music
        SET musicName=?,
            singerName=?,
            audioUrl=?,
            audioEncryptUrl=?,
            properUrlForUsed=?,
            audioLength=?,
            lyricUrl=?,
            lyricType=?,
            isLocal=?,
            isAdded=?,
            createTime=?,
            modifyTime=?,
            addToPlaylistTime=?,
            displaySingerName=?,
            musicTrackId=?
        WHERE identifier=?
        """
        
        do {
            try customDatabase.dbQueue.write({ db in
                for music in musicArray {
                    guard !music.identifier.isEmpty else {
                        CJAssert(false, "音乐 - 更新 - 音乐 id 不能为空")
                        continue
                    }
                    
                    try db.execute(sql: updateSql, arguments: [
                        music.musicName,
                        music.singerName,
                        music.audioUrl,
                        music.audioEncryptUrl,
                        music.properUrlForUsed,
                        music.audioLength,
                        music.lyricUrl,
                        music.lyricType.rawValue,
                        music.isLocal.intValue,
                        music.isAdded.intValue,
                        music.createTime,
                        music.modifyTime,
                        music.addToPlaylistTime,
                        music.displaySingerName,
                        music.musicTrackId,
                        music.identifier
                    ])
                }
            })
        } catch {
            CJAssert(false, "音乐 - 更新 - Error: \(error)")
        }
    }
    
    // MARK: - 查
    
    static func queryAllMusic() -> [CS_MusicModel] {
        var musicArray = [CS_MusicModel]()
        
        guard let customDatabase = CS_MusicStorageManager.shared.database else {
            CJLog("音乐 - 查询 - Error: 数据库未连接")
            return musicArray
        }
        
        let querySql = "select * from music limit \(kMusicMaxCount)"
        
        do {
            musicArray = try customDatabase.dbQueue.read { db in
                var tempMusicArray = [CS_MusicModel]()
                let tempResult = try Row.fetchAll(db, sql: querySql)
                
                for row in tempResult {
                    var rowDic = Dictionary(
                        row.map { (column, value) in (column, value.storage.value)},
                        uniquingKeysWith: { (left, _) in left }
                    ) as [String: Any]
                    
                    // id是数据库自带的id，不属于模型，服务端使用的字段同样是id，如果这里不移除，会导致解析模型时映射错误
                    rowDic.removeValue(forKey: "id")
                    
                    if let music = CS_MusicModel.deserialize(from: rowDic), !tempMusicArray.contains(where: { $0.identifier == music.identifier }) {
                        tempMusicArray.append(music)
                    }
                }
                
                return tempMusicArray
            }
        } catch {
            CJAssert(false, "音乐 - 查询 - Error: \(error)")
        }
        return musicArray
    }
    
    static func queryAllMusicAndSortByCreateTime() -> [CS_MusicModel] {
        var musicArray = [CS_MusicModel]()
        
        guard let customDatabase = CS_MusicStorageManager.shared.database else {
            CJLog("音乐 - 查询 - Error: 数据库未连接")
            return musicArray
        }
        
        let querySql = "select * from music order by createTime DESC limit \(kMusicMaxCount)"
        
        do {
            musicArray = try customDatabase.dbQueue.read { db in
                var tempMusicArray = [CS_MusicModel]()
                let tempResult = try Row.fetchAll(db, sql: querySql)
                
                for row in tempResult {
                    var rowDic = Dictionary(
                        row.map { (column, value) in (column, value.storage.value)},
                        uniquingKeysWith: { (left, _) in left }
                    ) as [String: Any]
                    
                    // id是数据库自带的id，不属于模型，服务端使用的字段同样是id，如果这里不移除，会导致解析模型时映射错误
                    rowDic.removeValue(forKey: "id")
                    
                    if let music = CS_MusicModel.deserialize(from: rowDic), !tempMusicArray.contains(where: { $0.identifier == music.identifier }) {
                        tempMusicArray.append(music)
                    }
                }
                
                return tempMusicArray
            }
        } catch {
            CJAssert(false, "音乐 - 查询 - Error: \(error)")
        }
        return musicArray
    }
    
    /// 查询是否是新音乐
    static func queryIsNewMusic(_ musicId: String) -> Bool {
        var isNewMusic = false
        
        guard !musicId.isEmpty else {
            CJLog("音乐 - 查询 - Error: 音乐为空")
            return isNewMusic
        }
        guard let customDatabase = CS_MusicStorageManager.shared.database else {
            CJLog("音乐 - 查询 - Error: 数据库未连接")
            return isNewMusic
        }
        let querySql = "select id from music where identifier = ?"
        
        do {
            isNewMusic = try customDatabase.dbQueue.read({ db in
                if let result = try Row.fetchOne(db, sql: querySql, arguments: [musicId]) {
                    let resultDic = Dictionary(result) { left, _ in left }
                    return resultDic.isEmpty
                }
                else {
                    return true
                }
            })
        } catch {
            CJAssert(false, "音乐 - 音乐查询 - Error: \(error)")
        }
        return isNewMusic
    }
    
    // MARK: - 私有方法
    
    /// 插入一首音乐到数据库
    private static func _insertMusicByDatabase(_ music: CS_MusicModel) throws {
        guard !music.identifier.isEmpty else {
            CJLog("音乐 - 插入 - 音乐 id 不能为空")
            return
        }
        guard let customDatabase = CS_MusicStorageManager.shared.database else {
            CJLog("音乐 - 插入 - Error: 数据库未连接")
            return
        }
        
        if music.createTime == 0 {
            music.createTime = Int(Date().timeIntervalSince1970)
        }
        if music.modifyTime == 0 {
            music.modifyTime = music.createTime
        }
        
        let isNew = queryIsNewMusic(music.identifier)
        if !isNew {
            CJLog("音乐 - 数据库 - 插入 - 音乐已存在 - \(music.identifier)")
            updateMusic(music)
            return
        }
        
        CJLog("音乐 - 数据库 - 插入 - 新音乐 - \(music.identifier)")
        
        let sql = """
        insert into music(
            musicTrackId,
            identifier,
            musicName,
            singerName,
            audioUrl,
            audioEncryptUrl,
            properUrlForUsed,
            audioLength,
            lyricUrl,
            lyricType,
            isLocal,
            isAdded,
            createTime,
            modifyTime,
            addToPlaylistTime,
            displaySingerName
        )
        values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
        """
        
        do {
            try customDatabase.dbQueue.write({ db in
                try db.execute(sql: sql, arguments: [
                    music.musicTrackId,
                    music.identifier,
                    music.musicName,
                    music.singerName,
                    music.audioUrl,
                    music.audioEncryptUrl,
                    music.properUrlForUsed,
                    music.audioLength,
                    music.lyricUrl,
                    music.lyricType.rawValue,
                    music.isLocal,
                    music.isAdded,
                    music.createTime,
                    music.modifyTime,
                    music.addToPlaylistTime,
                    music.displaySingerName
                ])
            })
        } catch {
            CJAssert(false, "音乐 - 音乐存储 - Error: \(error)")
        }
    }
    
}

